import os
import sqlite3
from abc import ABCMeta, abstractmethod

'''
This file provides the methods used by the meeting program to interface 
with persistent data objects. 
This release will only support an embedded database. However, given the 
class structure and object access model, additional storage types can 
be added later... 
'''

class dbEngines(object):
    '''
    Abstract base class for data layer interface.
    - Register the dbApi as an abstract base class
    This class can be used to interface with multiple 
    data storage methods...
    
    '''
    __metaclass__ = ABCMeta

    #def __init__(self, engineType):
    def __init__(self):
        #self.engineType = engineType
        self.dbName = 'MeetingData'
        self.db = None
        self.lastRowId = None
        self.rowCount = None
        self.lastQuery = None
        '''
        Note:
        Call the subclass openDB command....
        '''
        self.openDB()
        
    
    
    def getTableData(self, buildOption):
        '''
        Table definitions
        
        '''
        sql = {}
        statements = []
        statements.append("""
        CREATE TABLE users (
        uid integer primary key autoincrement,
        uname varchar(16) UNIQUE not null,
        password char(32) not null,
        namefirst varchar(32) not null,
        namelast varchar(32) not null,
        email varchar(128) not null, 
        isadmin integer
        );""")
        
        statements.append("""
        CREATE TABLE rooms (
        rid integer primary key autoincrement,
        rname varchar(32) unique,
        rdescription varchar(128),
        rsize integer
        );""")
        
        statements.append("""
        CREATE TABLE events (
        mid integer primary key autoincrement,
        mdescription varchar(64),
        mstarttime integer,
        mendtime integer,
        mhostid integer,
        mroomid integer,
        mstatusid integer
        );""")
        
        statements.append("""
        CREATE TABLE attendees (       
        mid integer ,
        uid integer ,
        asid integer,
        primary key (mid, uid)
        );""")
        
        statements.append("""
        CREATE TABLE astatus (       
        asid integer primary key autoincrement,
        asdescription varchar(32)
        );""")
        
        statements.append("""
        CREATE TABLE mstatus (       
        msid integer primary key autoincrement,
        msdescription varchar(32)
        );
        """)
        
        sql['create'] = statements
        
        statements = []
        
        statements.append("""
        drop TABLE users;
        """)
        statements.append("""
        drop TABLE rooms; 
        """)
        statements.append("""
        drop TABLE meetings; 
        """)
        statements.append("""
        drop TABLE attendees; 
        """)
        statements.append("""
        drop TABLE astatus;
        """)
        statements.append("""
        drop TABLE mstatus; 
        """)
        sql['drop'] = statements 
        
        return sql[buildOption]
    
    
    
    
    @abstractmethod    
    def db_interface_type(self):
        '''
        Require all derived classes to support an API call 
        for supporting factory model API requests
        via dbAPI interface...
        '''
        


    @abstractmethod    
    def initDB(self):
        '''
        Initialize DB
        '''
        print 'This is the super...'

    @abstractmethod
    def execute(self):
        '''
        Execute query
        ''' 
        
'''
Define Database Engine Types...
'''    
class sql3Impl(dbEngines):
    '''
    Implementation of SQLITE Database interface for 
    meeting program...
    ''' 
 
    
    def openDB(self):
        dbFileName = self.dbName + '.db3'
        try:
            self.db = sqlite3.connect(dbFileName, check_same_thread = False )
            '''
            Set the database to return row meta-data
            '''
            self.db.row_factory = sqlite3.Row
        except:
            print "Failed to open database" 
        
      
        '''
        Create cursor handle
        '''
        self.cursor = self.db.cursor()
        
    
    def initDB(self):
        '''
        Create the database...


        '''
        sqlSet = self.getTableData('create')
        for statement in sqlSet: 
            self.execute(statement)
    
    
    def rebuildDB(self):
        '''
        Drop and recreate database
        '''
        sqlSet = self.getTableData('drop')
        for statement in sqlSet: 
            self.execute(statement)
        self.initDB()
    
    @classmethod
    def db_interface_type(cls):
        '''
        Return the database type this object supports
        used in conjunction with dbApi factory class. 
        '''    
        engineType = 'sqlite3'  
        return  engineType    
    """        
    def initDB(self):
        '''
        Create the embedded database file
        and create a generic handler for the 
        database interface.
        '''
        
        ''' Call parent class to perform generic class operations'''
        super(sql3Impl, self).initDB()
        print "local db file = %s" %(dbFileName)

    """

    def execute(self, sql):
        '''
        Execute database query 
        '''

        cursor = self.db.cursor()
        try:
            with self.db:
                cursor.execute(sql)
                self.lastRowId = cursor.lastrowid
                
        except sqlite3.IntegrityError:
            print "Duplicate record found in database"
        except sqlite3.OperationalError, e:
            print "Duplicate record found in database [%s] " %(e) 

    def query(self, sql):
        '''
        Execute database query...
        '''
        cursor = self.db.cursor()
        result = cursor.execute(sql)
        return result

    def getLastRowId(self):
        '''
        '''
        return self.lastRowId
    



'''
Database Factory class interface...
returns database object to requester....
'''
class dbApi():
    '''
    Abstract class used to return the 
    a database engine type to the requesting 
    application
    '''
    '''
    -- Unit Tests --
    from db_interface import *
    foo = dbApi('sqlite3')
    handle = foo.open()
    '''
    
    
    def __init__(self, setDbType=None):
        '''
        set internal API data
        '''
        self.dbType = setDbType
        self.dbConnection = None
        self.dbTypeDict = {}
        #print "Inspecting given methods... "
        for subClass in dbEngines.__subclasses__():
            #print "Subclass = %s" %(subClass)
            thisType = subClass.db_interface_type()
            #print "This type found is -> %s" %(thisType)
            
            '''
            Store a local cache of database interfaces
            Note: This is a memory reference to the subclass containing 
            the object definition. In other words this is the uninstanciated
            class. 
            '''
            
            self.dbTypeDict[thisType] = subClass
    
    def getDbTypes(self):
        '''
        Return supported database types
        '''
        return self.dbTypeList 
    
    
    def getType(self):
        '''
        Return the configured database type
        '''
        return self.dbType
    
    def setType(self, value):
        '''
        change the database type
        '''
        self.dbType = value
        
    def open(self):
        '''
        Open connection to the database.
        '''
        try:
            '''
            Initialize the class and return the object...
            '''
            return self.dbTypeDict[self.dbType]()
        except KeyError:
            print "--Error--\nClass type not defined or invalid.\nGiven -> %s\nAvailable Classes (%s) " %(self.dbType, self.dbTypeDict.keys())  






class dbContent(dbApi):
    '''
    Concrete class for all defining database tables.
    '''
    

if __name__ == "__main__":
    '''
    Unit Tests for classes
    '''
    from db_interface import *
    foo = dbApi('sqlite3')
    db = foo.open()
    db.initDB()
    print "Database Initialized..."
    